import pandas
import cx_Oracle

from utils.log_util import logger


class InsertFromFile:

    def __init__(self):
        # 数据库连接信息
        # makedsn('oracle数据库ip', '数据库端口', '数据库名')
        # 业务处理平台（综台）测试库
        self.dsn = cx_Oracle.makedsn('10.6.56.53', '1521', 'ORATDB01')
        logger.info('>>>连接==业务处理平台（综台）测试==数据库')
        self.username = 'tongbao_uat'  # 数据库用户名
        self.password = 'X1zAy^k2H#EneN'  # 数据库密码

    def insert_from_excel(self, excel_file_path, table_name,schema_name=None):
        logger.info(f"开始向{schema_name}.{table_name}插入文件：{excel_file_path}")
        # 读取Excel文件，excel文件第一行填写的列名需要与数据库字段名一致
        df = pandas.read_excel(excel_file_path, skiprows=0)

        try:
            # 连接到Oracle数据库
            with cx_Oracle.connect(self.username, self.password, self.dsn) as connection:
                cursor = connection.cursor()

                # 假设DataFrame的列与数据库表的列一一对应
                # 构建插入语句的占位符
                columns = ', '.join(df.columns)
                placeholders = ', '.join([f':{col}' for col in df.columns])
                # 插入数据的SQL语句
                if schema_name:
                    insert_query = f"INSERT INTO {schema_name}.{table_name} ({columns}) VALUES ({placeholders})"
                else:
                    insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                # 将DataFrame的数据逐行插入数据库
                logger.info(insert_query)
                for index, row in df.iterrows():
                    converted_row = []
                    for col_value in row:
                        if isinstance(col_value, pandas.Timestamp):
                            converted_row.append(col_value.to_pydatetime())
                        elif col_value is pandas.NaT or col_value is None:
                            converted_row.append(None)
                        elif isinstance(col_value, float) and pandas.isna(col_value):
                            converted_row.append(None)
                        else:
                            converted_row.append(col_value)
                    logger.info(tuple(converted_row))
                    cursor.execute(insert_query, tuple(converted_row))
                # # 提交事务
                connection.commit()
                logger.info(">>>数据插入成功")
        except cx_Oracle.Error as e:
            logger.error(e)


insert = InsertFromFile()
